package com.web.order.dao;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.util.CommonUtil;
import com.web.hotel.po.City;
import com.web.order.po.OrderPo;
import com.web.order.service.OrderService;

@Service("orderService")
@Transactional(rollbackFor = { Exception.class })
public class OrderDAO implements OrderService {

	@Resource
	private SessionFactory sessionFactory;

	public boolean addOrder(OrderPo order) {
		// TODO Auto-generated method stub
		Session session = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			session.save(order);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

	public boolean updateOrder(OrderPo order) {
		// TODO Auto-generated method stub
		Session session = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			session.update(order);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

	public int deleteOrders(String orderIds) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			int count = 0;
			if (CommonUtil.isNotNull(orderIds)) {
				String sql = "delete from OrderPo where orderId in ("
						+ orderIds + ")";
				session = this.sessionFactory.getCurrentSession();
				query = session.createQuery(sql);
				count = query.executeUpdate();
			}
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

	public OrderPo getOrderByOrderId(Integer orderId) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			StringBuffer hql = new StringBuffer();
			hql.append("Select a From OrderPo a");
			hql.append(" left outer join fetch a.user");
			hql.append(" left outer join fetch a.hotel");
			hql.append(" where a.orderId = '");
			hql.append(orderId);
			hql.append("'");
			query = session.createQuery(hql.toString());
			OrderPo order = (OrderPo) query.list().get(0);
			return order;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public List<OrderPo> getOrderList(String sql, int first, int pageSize) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(sql);
			query.setFirstResult(first);
			query.setMaxResults(pageSize);
			List<OrderPo> orders = query.list();
			return orders;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	public int total(String condition) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			StringBuffer queryStringBuffer = new StringBuffer();
			queryStringBuffer.append("FROM OrderPo as o");
			queryStringBuffer.append(" left outer join fetch o.user as u");
			queryStringBuffer.append(" left outer join fetch o.hotel as h");
			queryStringBuffer.append(" where 1=1 ");
			queryStringBuffer.append(condition);
			query = session.createQuery(queryStringBuffer.toString());
			int count = query.list().size();
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;
	}

	@SuppressWarnings({ "unchecked" })
	public List<List<String>> getExportOrderList(String sql) {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		List<List<String>> result = new ArrayList<List<String>>();
		StringBuffer queryStringBuffer = new StringBuffer();
		queryStringBuffer.append("FROM OrderPo as o");
		queryStringBuffer.append(" left outer join fetch o.user as u");
		queryStringBuffer.append(" left outer join fetch o.hotel as h");
		queryStringBuffer.append(" where 1=1 ");
		if (CommonUtil.isNotNull(sql)) {
			queryStringBuffer.append(sql);
		}
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery(queryStringBuffer.toString());
			List<OrderPo> orders = query.list();

			if (null != orders && orders.size() > 0) {
				for (OrderPo o : orders) {
					List<String> temp = new ArrayList<String>();
					temp.add(o.getOrderId() + "");
					temp.add(o.getUser().getUserName());
					temp.add(o.getHotel().getHotelName());
					temp.add(CommonUtil.dateToString(o.getBeginTime()));
					temp.add(CommonUtil.dateToString(o.getEndTime()));
					temp.add(o.getOrderContext());
					temp.add(o.getPrice() + "");
					temp.add(CommonUtil.dateToString(o.getCreateTime()));
					String state = o.getState() + "";
					if ("0".equals(state)) {
						state = "已过期";
					} else if ("1".equals(state)) {
						state = "待取消";
					} else if ("2".equals(state)) {
						state = "已取消";
					} else if ("3".equals(state)) {
						state = "待支付";
					} else if ("4".equals(state)) {
						state = "已支付";
					}
					temp.add(state);
					temp.add(o.getComment());
					result.add(temp);
				}
			}
			return result;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	public List<?> getChart(String sql, int len) {
		// TODO Auto-generated method stub
		Session session = null;
		SQLQuery query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createSQLQuery(sql);
			query.addScalar("row", StandardBasicTypes.INTEGER);
			if (len == 2) {
				query.addScalar("count", StandardBasicTypes.INTEGER);
			} else if (len == 3) {
				query.addScalar("success", StandardBasicTypes.INTEGER);
				query.addScalar("fail", StandardBasicTypes.INTEGER);
			} else if (len == 4) {
				query.addScalar("success", StandardBasicTypes.INTEGER);
				query.addScalar("fail", StandardBasicTypes.INTEGER);
				query.addScalar("price", StandardBasicTypes.INTEGER);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<City> getCity() {
		// TODO Auto-generated method stub
		Session session = null;
		Query query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			query = session.createQuery("From City");
			return query.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}

	public boolean schedulerUpdate() {
		// TODO Auto-generated method stub
		Session session = null;
		SQLQuery query = null;
		try {
			session = this.sessionFactory.getCurrentSession();
			String sql = "update t_order set state = 0 where state = 3 and createTime < date_add(now(),interval -30 minute)";
			query = session.createSQLQuery(sql);
			query.executeUpdate();
			sql = "update t_room tr set tr.roomStatus = 0 where tr.roomStatus = 1 "
					+ "and tr.roomId in (select r.roomId from t_order o , t_order_room r "
					+ "where o.orderId = r.orderId and o.state = 0 and "
					+ "DATE_FORMAT(o.endTime,'%y%c%d') = DATE_FORMAT(now(),'%y%c%d') )";
			query = session.createSQLQuery(sql);
			query.executeUpdate();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}


	public int cancleOrders(String ids) {
		// TODO Auto-generated method stub
		Session session = null;
		SQLQuery query = null;
		try {
			int count = 0;
			if (CommonUtil.isNotNull(ids)) {
				String sql = "update t_order set state = 2 where orderId in ("
						+ ids + ")";
				session = this.sessionFactory.getCurrentSession();
				query = session.createSQLQuery(sql);
				count = query.executeUpdate();
			}
			return count;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

	public String getHomeIdsByOrder() {

		return null;
	}

	public void clearRoomToday() {
		// TODO Auto-generated method stub
		Session session = null;
		SQLQuery query = null;
		try {
			String sql = "update t_room tr set tr.roomStatus = 0 where tr.roomStatus = 1 and tr.roomId in "
					+ "(select r.roomId from t_order o , t_order_room r "
					+ "where o.orderId = r.orderId and o.endTime < now() and o.state = 4)";
			session = this.sessionFactory.getCurrentSession();
			query = session.createSQLQuery(sql);
			query.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

	public void clearRoomYesterday() {
		// TODO Auto-generated method stub
		Session session = null;
		SQLQuery query = null;
		try {
			String sql = "update t_room tr set tr.roomStatus = 0 where "
					+ "tr.roomStatus = 1 and tr.roomId in "
					+ "(select r.roomId from t_order o , t_order_room r "
					+ "where o.orderId = r.orderId and DATE_FORMAT(o.endTime,'%y%c%d') "
					+ " = DATE_FORMAT(date_sub(now(),interval 1 day),'%y%c%d') )";
			session = this.sessionFactory.getCurrentSession();
			query = session.createSQLQuery(sql);
			query.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			throw new RuntimeException();
		}
	}

}
